import json
import sqlite3

import yaml
from flask import Flask, render_template
import pandas as pd
import matplotlib.pyplot as plt
from wordcloud import WordCloud
from collections import defaultdict

app = Flask(__name__)
with open("config.yaml", 'r') as stream:
    config = yaml.safe_load(stream)

# 最老最新的电影
def find_oldest_newest_movies(filename):
    data = pd.read_excel(filename)

    oldest_movie = data.loc[data['上映时间'].idxmin()]
    newest_movie = data.loc[data['上映时间'].idxmax()]

    return oldest_movie, newest_movie


# 评分最高和评分最低的电影
def find_highest_lowest_ratings(filename):
    data = pd.read_excel(filename)

    highest_rating = data.loc[data['电影评分'].idxmax()]
    lowest_rating = data.loc[data['电影评分'].idxmin()]

    return highest_rating, lowest_rating


# 评分排名前10电影
def plot_top_ten_ratings(filename):
    plt.rcParams['font.sans-serif'] = ['Microsoft YaHei']
    plt.rcParams['axes.unicode_minus'] = False

    data = pd.read_excel(filename)
    data = data.sort_values('电影评分', ascending=False)
    top_ten = data.head(10)

    movie_titles = top_ten['电影名称']
    ratings = top_ten['电影评分']

    plt.bar(movie_titles, ratings)

    plt.title('评分排名前十的电影', fontsize=20)
    plt.xlabel('电影名称')
    plt.ylabel('评分')
    plt.xticks(rotation=45, ha='right')

    for i, rating in enumerate(ratings):
        plt.text(i, rating, str(rating), ha='center', va='bottom')

    plt.tight_layout()

    # 保存图像文件
    plt.savefig('static/top_ten_ratings.png')
    plt.close()


# 评分分布情况
def plot_histogram(filename):
    plt.rcParams['font.sans-serif'] = ['Microsoft YaHei']
    plt.rcParams['axes.unicode_minus'] = False

    data = pd.read_excel(filename)
    ratings = data['电影评分']

    bin_edges = [7, 7.5, 8, 8.5, 9, 9.5, 10]
    counts, _, _ = plt.hist(ratings, bins=bin_edges, edgecolor='black')

    plt.title('电影评分分布直方图', fontsize=20)
    plt.xlim(7, 10)
    plt.xlabel('评分')
    plt.ylabel('数量')
    plt.grid(linestyle='dotted', linewidth=1)

    plt.xticks(bin_edges)
    for i in range(len(counts)):
        label_x = (bin_edges[i] + bin_edges[i + 1]) / 2
        plt.annotate(str(int(counts[i])), xy=(label_x, counts[i]), xytext=(label_x, counts[i]), ha='center',
                     va='bottom')

    plt.tight_layout()

    # 保存图像文件
    plt.savefig('static/rating_distribution.png')
    plt.close()


# 生成各年份评分情况箱线图
def plot_boxplot(filename):
    plt.rcParams['font.sans-serif'] = ['Microsoft YaHei']
    plt.rcParams['axes.unicode_minus'] = False

    data = pd.read_excel(filename)
    x = data['上映时间']
    y = data['电影评分']

    time_bins = [1930, 1950, 1970, 1985, 1995, 2005, 2015, 2023]
    grouped_data = []
    for i in range(len(time_bins) - 1):
        start_year = time_bins[i]
        end_year = time_bins[i + 1]
        group = y[(x >= start_year) & (x < end_year)]
        grouped_data.append(group)

    plt.boxplot(grouped_data, labels=[f'{time_bins[i]}-{time_bins[i + 1]}' for i in range(len(time_bins) - 1)],
                patch_artist=True)

    plt.title('不同年份时间段电影评分箱线图', fontsize=20)
    plt.xlabel('时间段')
    plt.ylabel('评分')
    plt.grid(linestyle='dotted', linewidth=1)

    plt.tight_layout()

    # 保存图像文件
    plt.savefig('static/yearly_ratings_boxplot.png')
    plt.close()


# 生成词云图
def generate_wordcloud(filename):
    data = pd.read_excel(filename)
    txt = " ".join(str(i) for i in data['电影名称'].head(100))  # 获取前100部电影的名称
    wordcloud = WordCloud(width=800, height=400, background_color='white', font_path='msyh.ttc').generate(txt)

    plt.figure(figsize=(10, 5))
    plt.imshow(wordcloud, interpolation='bilinear')
    plt.axis('off')
    plt.title('猫眼TOP100电影词云图', fontsize=20)

    # 保存图像文件
    plt.savefig('static/wordcloud.png')
    plt.close()


# 获取Top100电影中演员出演次数前十名
def get_top_ten_actors(filename):
    data = pd.read_excel(filename)
    actor_movie_cnt = defaultdict(int)

    for index, row in data.iterrows():
        for actor in row['演出人员'].split(','):
            actor_movie_cnt[actor] += 1

    top_actors = sorted(actor_movie_cnt.items(), key=lambda x: x[1], reverse=True)[:10]

    return top_actors


# 主页路由
@app.route('/')
def index():
    # 读取电影数据
    filename = '猫眼TOP100.xls'

    # 获取最老和最新的电影
    oldest_movie, newest_movie = find_oldest_newest_movies(filename)

    # 获取评分最高和评分最低的电影
    highest_rating, lowest_rating = find_highest_lowest_ratings(filename)

    # 生成排名前十的电影图表
    plot_top_ten_ratings(filename)

    # 生成评分分布图表
    plot_histogram(filename)

    # 生成各年份评分箱线图
    plot_boxplot(filename)

    # 生成词云图
    generate_wordcloud(filename)

    # 获取Top100电影中演员出演次数前十名
    top_actors = get_top_ten_actors(filename)

    return render_template('index_new.html')


@app.route('/index')
def home():
    # return render_template("index.html")
    return index()

@app.route('/index_test')
def test():
    # 读取电影数据
    filename = '猫眼TOP100.xls'

    # 获取最老和最新的电影
    oldest_movie, newest_movie = find_oldest_newest_movies(filename)

    # 获取评分最高和评分最低的电影
    highest_rating, lowest_rating = find_highest_lowest_ratings(filename)

    # 生成排名前十的电影图表
    plot_top_ten_ratings(filename)

    # 生成评分分布图表
    plot_histogram(filename)

    # 生成各年份评分箱线图
    plot_boxplot(filename)

    # 生成词云图
    generate_wordcloud(filename)

    # 获取Top100电影中演员出演次数前十名
    top_actors = get_top_ten_actors(filename)
    return render_template('index.html', oldest_movie=oldest_movie, newest_movie=newest_movie,
                           highest_rating=highest_rating, lowest_rating=lowest_rating, top_actors=top_actors)


# 跳转hot100页面
@app.route('/hot100')
def movie():
    filename = '猫眼TOP100.xls'
    data = pd.read_excel(filename)
    data = data.sort_values('电影评分', ascending=False)
    top_ten = data.head(10)
    movie_titles = []
    ratings = []
    datalist = []
    da_name = config['database']['db_name']
    con = sqlite3.connect(da_name)
    cur = con.cursor()
    sql = "select * from {}".format(config['database']['table_name'])
    data = cur.execute(sql)
    for item in data:
        datalist.append(item)
    cur.close()
    con.close()
    # 将数据转换为字典
    data = {'movies': top_ten['电影名称'].values.tolist(), 'ratings': top_ten['电影评分'].values.tolist()}
    # 将数据转换为 JSON 字符串并进行引号转义
    json_data = json.dumps(data)
    return render_template("hot100.html", movies=datalist, top_10_name=movie_titles, top_10_rating=ratings, dd=json_data)


# 跳转top10
@app.route('/top10')
def score():
    return render_template("top10.html")


# 跳转词云页面
@app.route('/word')
def word():
    return render_template("word.html")


if __name__ == '__main__':
    app.run(debug=True)
